package com.supermap.dataservice.expertinfo.dao;

import com.supermap.dubbo.api.expertinfo.entity.ExpertInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

@Mapper
public interface ExpertInfoDao {
    /**
     * 获取某地区前n个活跃的专家列表，并返回活跃度
     * @param count 读取专家信息数量
     * @param areaCode 区域编码
     * @return
     */
    @Select("<script>" +
            "SELECT * FROM t_expertinfo\n" +
            "WHERE del_flg = 0\n" +
            "<if test='areaCodes != null'>\n" +
            "AND areaid IN\n" +
            "<foreach collection='areaCodes' open='(' close=')' item='areaCode' separator=','>\n" +
            "#{areaCode}\n" +
            "</foreach>\n" +
            "</if>" +
            "ORDER BY score DESC\n" +
            "<if test='count != null'>\n" +
            "LIMIT #{count}" +
            "</if>" +
            "</script>")
    public List<ExpertInfo> getPositiveExpertByArea(@Param("count") Integer count, @Param("areaCodes") String[] areaCode);


    /**
     * 根据时间和区域统计专家数量
     * @param areaCode 区域code
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return
     */
    @SelectProvider(type = ExpertInfoDaoProvider.class , method = "countByAreaCodeAndRangeDate")
    public List<Map<String , Object>> countByAreaCodeAndRangeDate(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);

    class ExpertInfoDaoProvider {

        public String countByAreaCodeAndRangeDate(String areaCode , String startDate , String endDate)
        {
            StringBuffer sql = new StringBuffer();
            sql.append("select if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname ,  count(t1.id) as counts from t_area a left join \n" +
                    "(\n" +
                    "\tselect t.id, u.areaCode , t.createtime from t_expertinfo t inner join t_user u on t.userid = u.id \n");
            if (startDate != null && !"".equals(startDate) && endDate != null && !"".equals(endDate))
            {
                sql.append(" where t.createtime between \'").append(startDate).append("\' and \'").append(endDate).append("\'");
            }
            sql.append(")t1 on a.areacode = t1.areaCode\n");
            if (areaCode != null && !"".equals(areaCode))
            {
                sql.append(" where a.areacode like ").append("\'").append(areaCode).append("%\'");
            }
            sql.append("group by  a.areaid");
            return  sql.toString();
        }
    }
}
